SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create FUNCTION [dbo].[BeginAmount](@DrugId nvarchar(50),@fromdate datetime)
Returns @BeginAmount table
(
	Quantity int,
	Amount decimal
)
AS
BEGIN
declare @in_total int
declare @in_totalAmount decimal
declare @Out_total int
declare @Out_totalAmount decimal
declare @Outadjust_total int
declare @Outadjust_totalAmount decimal
declare @Quantity int
declare @Amount decimal
---GRN
SET @in_total=( SELECT  sum(Quantity) as Quantity
from GRNDETAILS AS GD INNER JOIN GRN G ON G.GrnId=GD.GrnId 
where GrnDate < @fromdate and Drugid=@DrugId and G.IsComplete=1
group by DrugId)
SET @in_totalAmount=( SELECT  sum(Quantity*UnitPrice)
from GRNDETAILS AS GD INNER JOIN GRN G ON G.GrnId=GD.GrnId 
where GrnDate < @fromdate and Drugid=@DrugId and G.IsComplete=1
group by DrugId)
---Prescription
--quantity
SET @Out_total= (select  sum(quantity) as Quantity
from dbo.PrescriptionDetail AS PD INNER JOIN dbo.Prescription P ON P.PrescriptionID=PD.PrescriptionID
where DeliveryDate < @fromdate and Drugid=@DrugId
group by DrugId)
--amount
SET @Out_totalAmount= (select  sum(quantity*UnitPrice) as TotalAmount
from dbo.PrescriptionDetail AS PD INNER JOIN dbo.Prescription P ON P.PrescriptionID=PD.PrescriptionID
where DeliveryDate < @fromdate and Drugid=@DrugId
group by DrugId)
--OutAdjust
SET @Outadjust_total=(select  sum(quantity) as Quantity
from dbo.StockOutDetails AS SD INNER JOIN dbo.StockOut S ON S.StockOutId=SD.StockOutId
where StockOutDate < @fromdate and Drugid=@DrugId and S.IsComplete=1
group by DrugId)
--Amount
SET @Outadjust_totalAmount=(select  sum(quantity*UnitPrice) as TotalAmount
from dbo.StockOutDetails AS SD INNER JOIN dbo.StockOut S ON S.StockOutId=SD.StockOutId
where StockOutDate < @fromdate and Drugid=@DrugId and S.IsComplete=1
group by DrugId)

SET @Quantity=isnull(@in_total,0)-(isnull(@Out_total,0)+isnull(@Outadjust_total,0))
SET @Amount=isnull(@in_totalAmount,0)-(isnull(@Out_totalAmount,0)+isnull(@Outadjust_totalAmount,0))

Insert @BeginAmount values(@Quantity,@Amount)
return 
End

GO
